package org.example;

import org.example.pojo.Student;

import java.sql.*;
import java.util.ArrayList;

public class Main {
    public static void main(String[] args) throws SQLException {
        //1.加载驱动
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/cloud_user?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2b8&allowPublicKeyRetrieval=true";
        String username="root";
        String password="123456";

        //2.获取连接
        Connection connection = DriverManager.getConnection(url,username,password);
        Statement statement = connection.createStatement();

        //3.执行sql语句
        String sql = "select * from tb_student";
        String sql1 = "insert into tb_student(stu_name, stu_gender, stu_birthday, stu_username, stu_password) values ('张三','男','2022-01-01','zhangsan','123456')";
        String sql2 = "update tb_student set stu_name='李四' where id=1";
        String sql3 = "delete from tb_student where id=1";
        //4.执行查询，并且封装
        ResultSet re = statement.executeQuery(sql);
        ArrayList<Student> list = new ArrayList<Student>();
        while (re.next()) {
            Student student = new Student();
            student.setId(Integer.valueOf(re.getString("id")));
            student.setStuGender(re.getString("stu_gender"));
            student.setStuBrithday(re.getDate("stu_birthday"));
            student.setStuUsername(re.getString("stu_username"));
            student.setStuBrithday(re.getDate("stu_birthday"));
            student.setStuPassword(re.getString("stu_password"));
            list.add(student);
            System.out.println(re.getInt("id"));
            System.out.println(re.getString("stu_name"));
            System.out.println(re.getString("stu_gender"));
            System.out.println(re.getDate("stu_birthday"));
            System.out.println(re.getString("stu_username"));
            System.out.println(re.getString("stu_password"));
        }
        for (Student student : list) {
            System.out.println(student.getId());
            System.out.println(student.getStuGender());
            System.out.println(student.getStuBrithday());
            System.out.println(student.getStuUsername());
            System.out.println(student.getStuPassword());
        }

        //5.预编译,解决sql注入问题
        String sql4="select * from tb_student where id= ? ";
        PreparedStatement ps = connection.prepareStatement(sql4);//预编译方法
        ps.setInt(1,2);
        ResultSet re1 = ps.executeQuery();
        while (re1.next()) {
            System.out.println(re1.getInt("id"));
            System.out.println(re1.getString("stu_name"));
            System.out.println(re1.getString("stu_gender"));
            System.out.println(re1.getDate("stu_birthday"));
            System.out.println(re1.getString("stu_username"));
            System.out.println(re1.getString("stu_password"));
        }

        //关闭
        re.close();
        re1.close();//关闭
        statement.close();
        connection.close();
    }
}